package cn.sfa.service.routine;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat; 
import java.util.HashMap; 
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired; 
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import cn.sfa.common.pagination.Pagination;
import cn.sfa.common.utils.CommonUtil;
import cn.sfa.common.utils.DateUtil;
import cn.sfa.entity.routine.SalePrivateAffair; 
/**
 * 
* 类名称：DailyRoutineService   
* 类描述： 日常事务服务类
* 创建人：lenovo   
* 创建时间：2012-2-20 上午11:32:36   
* 修改人：lenovo   
* 修改时间：2012-2-20 上午11:32:36   
* 修改备注：   
* @version
 */
@Service
@Transactional
public class DailyRoutineService {
	
	@Autowired
	NamedParameterJdbcTemplate jdbcTemplate;

	/**
	 * 新建日常事务
	 * @param model
	 * @return
	 */
	public boolean addAffair(SalePrivateAffair model) {
		String sqlStr = "insert into SALEPRIVATEAFFAIR("
				+ "saleprivateaffairid, createuser, updateuser, affairitem, "
				+ "affairno, markdate, affairagentid, affairtype,affairstatus, affaircontent, isview,delmark)"
				+ "values(:saleprivateaffairid,:createuser,:updateuser,:affairitem,:affairno,:markdate,:affairagentid,:affairtype,:affairstatus,:affaircontent,:isview,:delmark)";	
		String markString =new SimpleDateFormat("yyyy-MM-dd").format(model.getMarkDate()); 	
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("saleprivateaffairid",CommonUtil.jugeAndFixNull(model.getSalePrivateAffairId()) );
		paramMap.put("createuser",CommonUtil.jugeAndFixNull(model.getCreateUid()) );
		paramMap.put("updateuser",CommonUtil.jugeAndFixNull(model.getUpdateId()) );
		paramMap.put("affairitem",model.getAffairItem());
		paramMap.put("affairno",CommonUtil.jugeAndFixNull(model.getAffairNo()) );
		paramMap.put("markdate", DateUtil.stringToDate(markString));
		paramMap.put("affairagentid",CommonUtil.jugeAndFixNull(model.getAffairAgentId()) );
		paramMap.put("affairtype",CommonUtil.jugeAndFixNull(model.getAffairType()) );
		paramMap.put("affairstatus",CommonUtil.jugeAndFixNull(model.getAffairStatus()) );
		paramMap.put("affaircontent",model.getAffairContent());
		paramMap.put("isview",CommonUtil.jugeAndFixNull(model.getIsView()) );
		paramMap.put("delmark", "N");
		return jdbcTemplate.update(sqlStr, paramMap) > 0; 
	}
	
	/**
	 * 编辑日常事务
	 * @param model
	 * @return
	 */
	public boolean updateAffair(SalePrivateAffair model){
		String sqlStr=
				"update SALEPRIVATEAFFAIR set " +
				"updateuser=:updateuser," +
				"updatedate=:updatedate," +
				"affairitem=:affairitem," +
				"markdate=:markdate," +
				"affairtype=:affairtype," +
				"affairstatus=:affairstatus," + 
				"affaircontent=:affaircontent," +
				"isview=:isview " + 
				"where " +
				"saleprivateaffairid=:saleprivateaffairid"; 
		Map<String, Object> paramMap = new HashMap<String, Object>(); 
		String markString =new SimpleDateFormat("yyyy-MM-dd").format(model.getMarkDate());
		String updateDateString =new SimpleDateFormat("yyyy-MM-dd").format(model.getUpdateDate());	
		paramMap.put("updateuser",model.getUpdateId());
		paramMap.put("updatedate",DateUtil.stringToDate(updateDateString));
		paramMap.put("affairitem",model.getAffairItem());
		paramMap.put("markdate",DateUtil.stringToDate(markString)); 
		paramMap.put("affairtype",model.getAffairType()); 
		paramMap.put("affairstatus",model.getAffairStatus()); 
		paramMap.put("affaircontent",model.getAffairContent());
		paramMap.put("isview",model.getIsView());
		paramMap.put("saleprivateaffairid",model.getSalePrivateAffairId());  
		return jdbcTemplate.update(sqlStr, paramMap) > 0; 
	}

	/**
	 * 得到日常事务
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @param currentuser
	 * @param affairType
	 * @param affairitem
	 * @param affaircontent
	 * @param affairstatus
	 * @param createdatebegin
	 * @param createdateend
	 * @return
	 * @author hui
	 */
	public Pagination SeachAffair(
			int currentPage,	//页码
			int numPerPage,		//页尺寸
			String url,			//分页 
			String currentuser,	//当前用户ID 
			String affairType,	//事务类型
			String affairitem,	//事务标题关键字
			String affaircontent,//事务内容关键字
			String affairstatus,//事务状态
			String createdatebegin,//创建时间始
			String createdateend//创建时间止 
			){  
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append("select * from table(pkg_sfa_saledaily.getDaily(:currentuser,:affairType,:affairitem,:affaircontent,:affairstatus,:createdatebegin,:createdateend))");
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("currentuser",currentuser);
		paramMap.put("affairType",affairType);
		paramMap.put("affairitem",affairitem);
		paramMap.put("affaircontent",affaircontent);
		paramMap.put("affairstatus",affairstatus);
		paramMap.put("createdatebegin",createdatebegin);
		paramMap.put("createdateend",createdateend); 
		return new Pagination(sqlStr.toString(),paramMap,currentPage,numPerPage,url,jdbcTemplate); 
  
	}
	/**
	 * 删除事务
	 * @param affairid
	 * @return
	 */
	public boolean delAffair(String affairid) {
		String sqlStr = "update saleprivateaffair set delmark='Y' where saleprivateaffairid = :affairid and delmark <> 'Y'";
		Map<String, Object> paramMap = new HashMap<String, Object>(); 
		paramMap.put("affairid", affairid); 
		return jdbcTemplate.update(sqlStr, paramMap) > 0; 
	}
	
	/**
	 * 分页
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @return
	 */
	public Pagination getPagination(int currentPage,int numPerPage,String url){
		
		String sqlStr = "select * from saleprivateaffair";

		return new Pagination(sqlStr,new HashMap<String,Object>(),currentPage,numPerPage,url,jdbcTemplate);
		
	}
	
	/**
	 * 根据ID获取详细信息
	 * @param affairid
	 * @return
	 */
	public SalePrivateAffair getAffairByID(String affairid){
		String sqlStr = "select saleprivateaffairid,createdate,createuser,updatedate,updateuser," +
				"affairitem,affairno,markdate,affairagentid,affairtype,affairstatus,affaircontent," +
				"affairresult,affaircustomerid,isaffix,isview,delmark " +
				"from saleprivateaffair " +
				"where saleprivateaffairid = :affairid";
		Map<String, Object> paramMap = new HashMap<String, Object>(); 
		paramMap.put("affairid", affairid);
		final SalePrivateAffair affair = new SalePrivateAffair();
		jdbcTemplate.query(sqlStr, paramMap, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				affair.setSalePrivateAffairId(rs.getString("saleprivateaffairid")); 
				affair.setCreateDate(rs.getDate("createdate")); 
				affair.setCreateUid(rs.getString("createuser")); 
				affair.setUpdateDate(rs.getDate("updatedate")); 
				affair.setUpdateId(rs.getString("updateuser")); 
				affair.setAffairItem(rs.getString("affairitem"));
				affair.setAffairNo(rs.getString("affairno")); 
				affair.setMarkDate(rs.getDate("markdate")); 
				affair.setAffairAgentId(rs.getString("affairagentid")); 
				affair.setAffairType(rs.getString("affairtype")); 
				affair.setAffairStatus(rs.getString("affairstatus")); 
				affair.setAffairContent(rs.getString("affaircontent"));  
				affair.setAffairResult(rs.getString("affairresult")); 
				affair.setAffairCustomerId(rs.getString("affaircustomerid")); 
				affair.setIsAffix(rs.getString("isaffix")); 
				affair.setIsView(rs.getString("isview")); 
				affair.setDelMark(rs.getString("delmark")); 
			}
		}); 
		return affair;
	}

	/**
	 * 修改事务状态
	 * @param affairid
	 * @param affairStatus
	 * @return
	 */
	public boolean setAffairStatus(String affairid, String affairStatus) {
		String sqlStr = "update SALEPRIVATEAFFAIR set AFFAIRSTATUS=:AFFAIRSTATUS  where  saleprivateaffairid=:saleprivateaffairid";
		Map<String, Object> paramMap = new HashMap<String, Object>(); 
		paramMap.put("AFFAIRSTATUS", affairStatus);
		paramMap.put("saleprivateaffairid", affairid);
		return jdbcTemplate.update(sqlStr, paramMap) > 0; 
	}
}
